/*
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements.  See the NOTICE file
distributed with this work for additional information
regarding copyright ownership.  The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License at

 http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.    
*/


/* Name
' SQLDBConnection
' Matthew Tuffin 23/08/2007
' Desc
 * Microsoft SQL Database Class for interfacing with Microsoft SQL Server
'
' Requirements:
 * System.Data 
 * System.Collections
 * System.Windows.Forms
 * System.Data.SqlClient
 * Logger
'
' Usage:
 * Create a new object from this class. The default constructor is not supported. You have to create a connectionstring
 * either through the builder or supply one
'
' Limitations:
 * 
'
*/

using System;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Logger;

namespace DBConnection
{
    /// <summary>
    /// Please note do not use the default constructor of this class.
    /// </summary>
    public class SQLDBConnection : IDBSourceConnection
    {
        //Local pointer to ConnStringBuilder
        private SqlConnectionStringBuilder mp_ConnStringBuilder;
        //local pointer to Connection
        private SqlConnection mp_SQLDBConn;
        //local pointer to CommandBuilder
        private SqlCommandBuilder mp_SQLCommandBuilder;
   
        /// <summary>
        /// Default Constructor is not supported
        /// </summary>
        public SQLDBConnection() : base ()
        {
            try
            {
                throw (new Exception("Can not create connection with out connection string"));
            }
            catch (Exception ex)
            {
                CMsgBox.ShowMsgBox(ex.Message);
                Application.Exit();
            }
        }

        /// <summary>
        /// Constructor that accepts a connection string
        /// </summary>
        /// <param name="strConnectionString">Connection String</param>
        public SQLDBConnection(string strConnectionString)
            : base(strConnectionString)
        {
            SetConnBuilder();
            mp_ConnStringBuilder.ConnectionString = m_strlocalConnString;
            SetCommandBuilder();
            SetConnection(m_strlocalConnString);
        }

        /// <summary>
        /// Construtor with a datasource and database specified
        /// </summary>
        /// <param name="strDataSource">DataSource</param>
        /// <param name="strInitDB">Database</param>
        public SQLDBConnection(string strDataSource, string strInitDB)
        {
            SetConnBuilder();
            SetConnectionProperties(strDataSource, strInitDB, true);
            m_strlocalConnString = mp_ConnStringBuilder.ConnectionString;
            SetCommandBuilder();
            SetConnection(m_strlocalConnString);
        }

        /// <summary>
        /// Deconstructor for SQLDB will generate stats need to move stats off to a log file
        /// </summary>
        ~SQLDBConnection()
        {
            IDictionary Dictionary;
            if (mp_SQLDBConn != null)
            {
                Dictionary = mp_SQLDBConn.RetrieveStatistics();
                foreach (object key in Dictionary.Keys)
                {
                    Console.WriteLine("{0}: {1}", key, Dictionary[key]);
                }
             }
        }

        /// <summary>
        /// Creates a new SQLConnection Object
        /// </summary>
        /// <param name="strConnString">Connection String</param>
        private void SetConnection(string strConnString)
        {
            base.SetConnection(typeof(System.Data.SqlClient.SqlConnection), strConnString);
         //   CommunicateToServer(new RunConnectionData(SetSchemaTables));
            mp_SQLDBConn = (SqlConnection)m_DBConnection;
        }

        /// <summary>
        /// Creates a new SQLConnectionBuilder
        /// </summary>
        private void SetConnBuilder()
        {
            base.SetConnBuilder(typeof(System.Data.SqlClient.SqlConnectionStringBuilder));
            mp_ConnStringBuilder = (SqlConnectionStringBuilder)m_DBConnStringBuilder;
        }

        /// <summary>
        /// Creates a new SQLCommandBuilder
        /// </summary>
        private void SetCommandBuilder()
        {
            base.SetCommandBuilder(typeof(System.Data.SqlClient.SqlCommandBuilder));
            mp_SQLCommandBuilder = (SqlCommandBuilder)m_DBCommandBuilder;                
        }

        /// <summary>
        /// Sets the Connection Properties in ConnStringBuilder
        /// </summary>
        /// <param name="strDataSource">DataSource</param>
        /// <param name="strInitDB">Database</param>
        /// <param name="bTrustedConn">Intergrated Security</param>
        public void SetConnectionProperties(string strDataSource, string strInitDB, bool bTrustedConn)
        {
            mp_ConnStringBuilder.DataSource = strDataSource;
            mp_ConnStringBuilder.InitialCatalog = strInitDB;
            mp_ConnStringBuilder.IntegratedSecurity = bTrustedConn;
            mp_ConnStringBuilder.AsynchronousProcessing = true;
            if (bConnectionDisabled)
            {
                SetConnection(mp_ConnStringBuilder.ConnectionString);
            }
        }

        /// <summary>
        /// Sets a username and password for a connectionstring
        /// </summary>
        /// <param name="strUsername">Username</param>
        /// <param name="strPassword">Password</param>
        public void SetUserPassword(string strUsername, string strPassword)
        {
            mp_ConnStringBuilder.UserID = strUsername;
            mp_ConnStringBuilder.Password = strPassword;
            SetConnection(mp_ConnStringBuilder.ConnectionString);
        }

        /// <summary>
        /// Adds a new SQL Permission
        /// </summary>
        /// <param name="strConnString">Connection String</param>
        /// <param name="strRestrictions">Retrictions</param>
        /// <param name="Behaviour">KeyRestrictionBehavior defines type of behaviour</param>
        public void AddSQLPermissions(string strConnString, string strRestrictions, System.Data.KeyRestrictionBehavior Behaviour)
        {
            SqlClientPermission sqlPermission = (SqlClientPermission) base.AddPermission(strConnString, strRestrictions, Behaviour,typeof(System.Data.SqlClient.SqlClientPermission));
            m_ListPermissions.Add(sqlPermission);
        }

        /// <summary>
        /// Clears the SQL Connection Pools
        /// </summary>
        public void ClearSQLPools()
        {
            SqlConnection.ClearAllPools();                        
        }

        /// <summary>
        /// Requests a sqldatareader given a query
        /// </summary>
        /// <param name="strQuery">Select Query</param>
        /// <returns>sqlDataReader</returns>
        public SqlDataReader RequestReader(string strQuery)
        {
            SqlDataReader Reader = (SqlDataReader)ReadSQLIntoDataReader(strQuery);
            return Reader;
        }

        /// <summary>
        /// Exeception Message for SQL Server
        /// </summary>
        /// <param name="ee">Exception</param> 
        public override void SQLExceptionMessageBox(DbException ee)
        {
            string errorMessage;
            SqlException ex = (SqlException)ee;

            for (int i = 0; i < ex.Errors.Count; i++)
            {
                errorMessage = ("Index #" + i + "\n" +
                    "Message: " + ex.Errors[i].Message + "\n" +
                    "LineNumber: " + ex.Errors[i].LineNumber + "\n" +
                    "Source: " + ex.Errors[i].Source + "\n" +
                    "Procedure: " + ex.Errors[i].Procedure + "\n");

                CMsgBox.ShowMsgBox(errorMessage);
            }
        }

        /// <summary>
        /// Requests an Async Command be executed to return a reader. This will run in the background 
        /// until you retrive it with RetriveAsyncReader
        /// </summary>
        /// <param name="Command">SqlCommand</param>
        /// <param name="callback">A function Callback assoicated with it, null if none required</param>
        /// <returns>ThreadID</returns>
        public int RequestASyncReader(SqlCommand Command, AsyncCallback callback)
        {
            m_ListASyncResult.Add(Command.BeginExecuteReader(callback, Command, CommandBehavior.CloseConnection));
            return (m_ListASyncResult.Count - 1);
        }

        /// <summary>
        /// Retrive the result from an Async Command
        /// </summary>
        /// <param name="iAysncIndex">ThreadID</param>
        /// <param name="Command">SqlCommand</param>
        /// <returns>DataReader</returns>
        public SqlDataReader RetriveAsyncReader(int iAysncIndex, SqlCommand Command)
        {
            SqlDataReader Reader = Command.EndExecuteReader(m_ListASyncResult[iAysncIndex]);
            m_ListASyncResult.RemoveAt(iAysncIndex);
            return (Reader);
        }

        /// <summary>
        /// Requests an Async Command be executed . This will run in the background 
        /// until you retrive it with RetriveAsyncReader
        /// </summary>
        /// <param name="callback">A function Callback assoicated with it, null if none required</param>
        /// <param name="Command">SqlCommand</param>
        /// <returns>ThreadID</returns>        
        public int RequestASyncQuery(SqlCommand Command, AsyncCallback callback)
        {
            m_ListASyncResult.Add(Command.BeginExecuteNonQuery(callback, Command));
            return (m_ListASyncResult.Count - 1);
        }

        /// <summary>
        /// Ends the request to the AsyncQuery
        /// </summary>
        /// <param name="iAysncIndex">ThreadID</param>
        /// <param name="Command">SqlCommand</param>
        public void EndASyncQuery(int iAysncIndex, SqlCommand Command)
        {
            Command.EndExecuteNonQuery(m_ListASyncResult[iAysncIndex]);
            m_ListASyncResult.RemoveAt(iAysncIndex);
        }

        /// <summary>
        /// Add a Parameter to an SQLCommand please note parameters are @parameter in SQL-SERVER
        /// </summary>
        /// <param name="Command">Command</param>
        /// <param name="strParameterName">ParameterName</param>
        /// <param name="DBType">DataType</param>                
        public void AddParameterToSQLCommand(SqlCommand Command, string strParameterName, SqlDbType DBType)                                           
        {
            SqlParameter parameter = (SqlParameter)CreateNewParameter(typeof(System.Data.SqlClient.SqlParameter));
            parameter.ParameterName = strParameterName;
            parameter.SqlDbType = DBType;
            Command.Parameters.Add(parameter);           
        }

        /// <summary>
        /// Adds a Value to a parameter 
        /// </summary>
        /// <param name="Command">Command</param>
        /// <param name="strParameterName">ParameterName</param>
        /// <param name="oValue">Value to be put in</param>
        public void AddValueToParameterViaName(SqlCommand Command, string strParameterName, object oValue)
        {
            Command.Parameters[strParameterName].Value = oValue;            
        }

        /// <summary>
        /// Adds a callback to a command when it has been completed
        /// </summary>
        /// <param name="Command">SqlCommand</param>
        /// <param name="EventHandler">EventHandler</param>
        public void AddCallbackWhenCommandComplete(SqlCommand Command, StatementCompletedEventHandler EventHandler)
        {            
            Command.StatementCompleted += EventHandler;
        }

        //void Command_StatementCompleted(object sender, StatementCompletedEventArgs e)
        //{
        //    throw new Exception("The method or operation is not implemented.");
        //}

        /// <summary>
        /// Requests a datatable using a new adapter
        /// </summary>
        /// <param name="strQuery">Select Query</param>
        /// <param name="strAdapterName">New AdapterName</param>
        /// <returns>DataTable</returns>
        public override DataTable RequestDataTableUsingSQLAdapter(string strQuery, string strAdapterName)
        {
            SqlDataAdapter sqlAdapter;            
            SetAdapter(typeof(System.Data.SqlClient.SqlDataAdapter), strAdapterName);
            DataTable DT = SelectSQLIntoDataTable(strQuery, strAdapterName);
            sqlAdapter = (SqlDataAdapter)m_DictionaryDataAdapters[strAdapterName];

            sqlAdapter.RowUpdating += new SqlRowUpdatingEventHandler(DefaultsqlAdapter_RowUpdating);
            sqlAdapter.RowUpdated += new SqlRowUpdatedEventHandler(DefaultsqlAdapter_RowUpdated);
            return (DT);
        }            

        /// <summary>
        /// RowUpdated Event
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void DefaultsqlAdapter_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
        {
            //throw new Exception("The method or operation is not implemented.");
        }

        /// <summary>
        /// RowUpdating Event
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void DefaultsqlAdapter_RowUpdating(object sender, SqlRowUpdatingEventArgs e)
        {
           // throw new Exception("The method or operation is not implemented.");
        }


        /// <summary>
        ///  Converts a datetime structure to a SQL Server set date  
        /// </summary>
        /// <param name="Time">DateTime</param>
        /// <returns>string</returns>
        public static string DateTimeToSQLDateTime(DateTime Time)
        {
            string date = Time.Month + "/" + Time.Day + "/" + Time.Year;

            return date;
        }

        /// <summary>
        /// Alternative Auto Generates an update, delete and insert statement from an adapter with a select statement
        /// </summary>
        /// <param name="strAdapterName">Adpater Name</param>
        /// <param name="DT">DataTable</param>
        /// <param name="strActualTableName">The Actual Name of the Table</param>
        /// <returns>true if successful</returns>
        public bool AutoGenUpdateDeleteInsertAlternativeForMSSQL(string strAdapterName, ref DataTable DT, string strActualTableName)
        {
            try
            {
                if (bConnectionDisabled)
                {
                    throw (new Exception("Connection has been disabled please re-run the TestConnection to correct this"));
                }
                CustomCommandBuilder CommandBuiler = new CustomCommandBuilder(DT, mp_SQLDBConn, strActualTableName);
                SqlDataAdapter Adapter = (SqlDataAdapter)m_DictionaryDataAdapters[strAdapterName];
                Adapter.DeleteCommand = CommandBuiler.DeleteCommand;
                Adapter.UpdateCommand = CommandBuiler.UpdateCommand;
                Adapter.InsertCommand = CommandBuiler.InsertCommand;                
                return (true);
            }
            catch (Exception ex)
            {
                CMsgBox.ShowMsgBox(ex.Message);
                ErrorLog.HandleException(ex);
                return (false);
            }
        }       
    }
}
